/**
 * Project: lionbule-other
 * 
 * File Created at 2012-6-28
 * $Id$
 * 
 * Copyright 1999-2100 Alibaba.com Corporation Limited.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * Alibaba Company. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Alibaba.com.
 */
package com.dm;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * DataModel.
 * 
 * @author LionBule 2012-6-28 下午8:53:12
 */
public class DataModel4File {
    // 行内容分割符 Ctrl+A ^A
    public static final char DW_SEPARATOR_CHAR = 1;
    // NUll的替换值 Ctrl+B ^B
    public static final char DW_NULL_CHAR      = 2;

    public static void main(String[] args) throws Exception {
        String filepath = "d:/tmp/DM-junjia-(V1.2) -test.xlsx";
        String storePath = "d:/tmp/alerm_propertys_test.sql";
        String dbFilePath = "d:/tmp/RKADM_AGDS_CATEGORY_TRAN_FATMT0.dat";

        String[] sheetArrayTest = new String[] { "类目" };

        // Db file
        List<TempDO> cols = getColumn(sheetArrayTest, filepath);
        mockDbFile(cols, dbFilePath);

        // do
         List<String> sqls = doAction(sheetArrayTest, filepath);
         write(sqls, storePath);

        System.out.println("ok");
    }

    @SuppressWarnings("restriction")
    static void write(List<String> sqls, String storePath) throws IOException {
        FileWriter fw = new FileWriter(storePath);
        String lineSeparator = (String) java.security.AccessController
                .doPrivileged(new sun.security.action.GetPropertyAction("line.separator"));

        for (String s : sqls) {
            fw.write(s);
            fw.write(lineSeparator);
        }

        fw.close();
    }

    static void mockDbFile(List<TempDO> cols, String filePath) throws IOException {

        FileWriter fw = new FileWriter(filePath);

        StringBuilder sbHead = new StringBuilder();
        sbHead.append("ID");
        for (TempDO d : cols) {
            sbHead.append(DW_SEPARATOR_CHAR).append(d.colName);
        }
        sbHead.append("\n");
        fw.write(sbHead.toString());

        for (int i = 1; i <= 10; i++) {
            StringBuilder sbBody = new StringBuilder();
            sbBody.append(i);
            for (TempDO d : cols) {
                String value = null;
                if (d.type == 6) {// int
                    value = String.valueOf((int) (Math.random() * 10000));
                } else if (d.type == 2) { // double
                    value = String.valueOf(Math.random() * 1000);
                } else {
                    throw new RuntimeException(d.colName);
                }
                sbBody.append(DW_SEPARATOR_CHAR).append(value);
            }
            sbBody.append("\n");

            fw.write(sbBody.toString());
        }

        // test null
        StringBuilder sbBody = new StringBuilder();
        sbBody.append(11);
        for (TempDO d : cols) {
            sbBody.append(DW_SEPARATOR_CHAR).append(DW_NULL_CHAR);
        }
        // sbBody.append("\n");
        fw.write(sbBody.toString());

        fw.close();
    }

    static List<TempDO> getColumn(String[] sheetArray, String filepath) throws Exception,
            IOException {
        List<TempDO> result = new ArrayList<TempDO>();

        Workbook wb = WorkbookFactory.create(new File(filepath));
        Map<String, Integer> typeMapping = getTypeMapping();
        for (String sheetName : sheetArray) {
            Sheet tmpSheet = wb.getSheet(sheetName);
            for (Row row : tmpSheet) {
                int rownum = row.getRowNum();
                if (rownum == 0) {
                    continue;
                }
                double index = row.getCell(0).getNumericCellValue();
                if (index < 1) {
                    continue;
                }
                String type = row.getCell(4).getStringCellValue().trim().toUpperCase();
                String colName = row.getCell(6).getStringCellValue().trim().toUpperCase();

                Integer typeNum = typeMapping.get(type);
                if (typeNum == null) {
                    throw new IllegalArgumentException("typeNum is null. " + colName);
                }
                TempDO d = new TempDO(colName, typeNum);
                result.add(d);
            }
        }

        return result;
    }

    static List<String> doAction(String[] sheetArray, String filepath) throws Exception {
        List<String> sqls = new ArrayList<String>();

        Map<String, Integer> paths = PathService.queryAllPath();
        Map<String, Integer> typeMapping = getTypeMapping();

        Workbook wb = WorkbookFactory.create(new File(filepath));
        for (String sheetName : sheetArray) {
            Sheet tmpSheet = wb.getSheet(sheetName);
            for (Row row : tmpSheet) {
                int rownum = row.getRowNum();
                if (rownum == 0) {
                    continue;
                }
                double index = row.getCell(0).getNumericCellValue();
                if (index < 1) {
                    continue;
                }
                String name = row.getCell(1).getStringCellValue().trim();
                String tmpPath = row.getCell(2).getStringCellValue().trim().toUpperCase();;
                String code = row.getCell(3).getStringCellValue().trim().toUpperCase();
                String type = row.getCell(4).getStringCellValue().trim().toUpperCase();
                String tabName = null;
                String colName = null;
                if (!sheetName.equals("Realtime")) {
                    tabName = row.getCell(5).getStringCellValue().trim().toUpperCase();;
                    colName = row.getCell(6).getStringCellValue().trim().toUpperCase();
                }

                String errMsg = new StringBuilder().append(name).append(",").append(tmpPath)
                        .append(",").append(code).append(",").append(type).append(",")
                        .append(tabName).append(",").append(colName).toString();

                Integer parentId = paths.get(tmpPath);
                if (parentId == null || parentId <= 0) {
                    throw new IllegalArgumentException("parentId is error, " + errMsg);
                }
                Integer typeNum = typeMapping.get(type);
                if (typeNum == null) {
                    throw new IllegalArgumentException("typeNum is null. " + errMsg);
                }

                String sql = getSql(parentId, code, name, typeNum, tabName, colName);
                sqls.add(sql);
            }
        }

        return sqls;
    }

    static String getSql(int parentId, String code, String name, int type, String tableName,
                         String colName) {
        StringBuilder sb = new StringBuilder();
        sb.append(
                "insert into agds_dm_property (path_id,pcode,pname,data_type,dw_table,dw_column,status,creater,operator,gmt_create,gmt_modified)")
                .append(" values (").append(parentId).append(",'").append(code).append("','")
                .append(name).append("',").append(type).append(",'").append(tableName)
                .append("', '").append(colName)
                .append("',3,'qingyan.maqy','qingyan.maqy',now(),now()").append(");");
        return sb.toString();
    }

    static Map<String, Integer> getTypeMapping() {
        Map<String, Integer> rs = new HashMap<String, Integer>();
        rs.put("Long".toUpperCase(), 1);
        rs.put("Double".toUpperCase(), 2);
        rs.put("String".toUpperCase(), 3);
        rs.put("Boolean".toUpperCase(), 4);
        rs.put("Date".toUpperCase(), 5);
        rs.put("Calendar".toUpperCase(), 5);
        rs.put("Integer".toUpperCase(), 6);

        return rs;
    }
}

class TempDO {
    public String  colName;
    public Integer type;

    public TempDO(String colName, Integer type) {
        this.colName = colName;
        this.type = type;
    }

}
